CREATE OR REPLACE EDITIONABLE PACKAGE BODY "SCOTT"."PKG_ROBOT3" as

  -- 根据当日持有股票的收盘价，在卖股票/买股票之前，更新robot3_account表
  -- 游标名称update_robot_account_before_sell_or_buy
  procedure update_robot_account_b_s_b(p_date in varchar2) as
  begin
    declare
      -- 股票资产
      v_stock_assets number;
      -- 账户名称
      v_robot_name varchar2(20);
      -- 某只股票的当前收盘价
      v_current_close_price number;
      -- robot3_account表的所有记录
      cursor cur_robot_account is
        select * from robot3_account;
      -- 某个账户所有没有卖出的股票。用于做多
      -- 游标名称：cur_bull_robot_stock_transaction_record
      cursor cur_bull_r_s_t_r is
        select *
          from robot3_stock_transact_record t
         where t.robot_name = v_robot_name
           and t.sell_date is null
           and t.sell_price is null
           and t.sell_amount is null
           and t.direction = 1;
      -- 某个账户所有没有买入的股票。用于做空
      -- 游标名称：cur_short_robot_stock_transaction_record
      cursor cur_short_r_s_t_r is
        select *
          from robot3_stock_transact_record t
         where t.robot_name = v_robot_name
           and t.buy_date is null
           and t.buy_price is null
           and t.buy_amount is null
           and t.direction = -1;
    begin
      for row_robot_account in cur_robot_account loop
        v_robot_name          := row_robot_account.robot_name;
        v_stock_assets        := 0;
        v_current_close_price := null;
      
        -- 做多
        -- 计算这个账户的所有股票的股票资产
        for row_robot_stock_transac_record in cur_bull_r_s_t_r loop
          -- 需要重置为null，否则会留有一个股票的价格
          v_current_close_price := null;
          -- 查找某只股票在某一天的收盘价
          begin
            select t.close_price
              into v_current_close_price
              from stock_transaction_data_all t
             where t.code_ = row_robot_stock_transac_record.stock_code
               and t.date_ = to_date(p_date, 'yyyy-mm-dd');
          exception
            when no_data_found then
              v_current_close_price := null;
              -- 说明股票在这一天没有交易记录
              DBMS_OUTPUT.put_line('股票【' ||
                                   row_robot_stock_transac_record.stock_code ||
                                   '】在日期【' || p_date || '】没有交易记录');
          end;
          -- 如果在某一天没有收盘价，比如停牌，则查找最近一个交易日的收盘价
          if v_current_close_price is null then
            select std.close_price
              into v_current_close_price
              from (select *
                      from stock_transaction_data_all t
                     where t.code_ =
                           row_robot_stock_transac_record.stock_code
                       and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                     order by t.date_ desc) std
             where rownum <= 1;
          end if;
        
          -- 计算这只股票的股票资产
          v_stock_assets := v_stock_assets +
                            v_current_close_price *
                            row_robot_stock_transac_record.buy_amount;
        
        end loop;
      
        -- 做空
        -- 计算这个账户的所有股票的股票资产
        for row_robot_stock_transac_record in cur_short_r_s_t_r loop
          -- 需要重置为null，否则会留有一个股票的价格
          v_current_close_price := null;
          -- 查找某只股票在某一天的收盘价
          begin
            select t.close_price
              into v_current_close_price
              from stock_transaction_data_all t
             where t.code_ = row_robot_stock_transac_record.stock_code
               and t.date_ = to_date(p_date, 'yyyy-mm-dd');
          exception
            when no_data_found then
              v_current_close_price := null;
              -- 说明股票在这一天没有交易记录
              DBMS_OUTPUT.put_line('股票【' ||
                                   row_robot_stock_transac_record.stock_code ||
                                   '】在日期【' || p_date || '】没有交易记录');
          end;
          -- 如果在某一天没有收盘价，比如停牌，则查找最近一个交易日的收盘价
          if v_current_close_price is null then
            select std.close_price
              into v_current_close_price
              from (select *
                      from stock_transaction_data_all t
                     where t.code_ =
                           row_robot_stock_transac_record.stock_code
                       and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                     order by t.date_ desc) std
             where rownum <= 1;
          end if;
        
          -- 计算这只股票的股票资产
          v_stock_assets := v_stock_assets +
                            (row_robot_stock_transac_record.sell_price -
                            v_current_close_price) *
                            row_robot_stock_transac_record.sell_amount +
                            row_robot_stock_transac_record.sell_price *
                            row_robot_stock_transac_record.sell_amount;
        
        end loop;
      
        -- 更新robot3_account表的stock_assets、total_assets字段
        if v_stock_assets is not null and v_stock_assets != 0 then
          update robot3_account t
             set t.stock_assets = v_stock_assets,
                 t.total_assets = t.capital_assets + v_stock_assets
           where t.robot_name = row_robot_account.robot_name;
          commit;
        end if;
      end loop;
    end;
  end update_robot_account_b_s_b;

  --------------------------------------------------------------------------------------------------

  -- 卖股票/买股票，只修改robot3_stock_transact_record表中的记录
  /* p_mandatory_stop_loss等于1，表示有强制止损；p_mandatory_stop_loss等于0，表示没有强制止损 */
  procedure sell_or_buy(p_date                     in varchar2,
                        p_mandatory_stop_loss      in number,
                        p_mandatory_stop_loss_rate in number) as
  begin
    declare
      -- 机器人账户名称
      v_robot_name varchar2(20);
      -- 卖出/买入价格
      v_sell_or_buy_price number;
      -- 是否卖出/买入。1表示准备卖出/买入，0表示不准备卖出/买入
      v_do_sell_buy number := 0;
      -- 买股票的算法
      -- v_sell_standard number;
      -- robot_account表的所有记录
      cursor cur_robot_account is
        select * from robot3_account;
      -- 某个机器人账户的买入的股票的交易记录
      -- 游标名称 cur_bull_robot_stock_transaction_record
      cursor cur_bull_r_s_t_r is
        select *
          from robot3_stock_transact_record t
         where t.robot_name = v_robot_name
           and t.SELL_DATE is null
           and t.SELL_PRICE is null
           and t.SELL_AMOUNT is null
           and t.direction = 1;
      -- 某个机器人账户的卖出的股票的交易记录
      -- 游标名称：cur_short_robot_stock_transaction_record
      cursor cur_short_r_s_t_r is
        select *
          from robot3_stock_transact_record t
         where t.robot_name = v_robot_name
           and t.BUY_DATE is null
           and t.BUY_PRICE is null
           and t.BUY_AMOUNT is null
           and t.direction = -1;
    begin
      for row_robot_account in cur_robot_account loop
      
        -- 如果这个账户没有持有股票，则查找下一个账户
        if row_robot_account.hold_stock_number = 0 then
          continue;
        end if;
      
        v_robot_name := row_robot_account.robot_name;
      
        -- 做多
        for row_bull_r_s_t_r in cur_bull_r_s_t_r loop
        
          -- 判断是否有强制止损
          if p_mandatory_stop_loss = 1 then
            begin
              select t1.close_price
                into v_sell_or_buy_price
                from stock_transaction_data_all t1
               where t1.code_ = row_bull_r_s_t_r.stock_code
                 and t1.date_ = to_date(p_date, 'yyyy-mm-dd');
              if (v_sell_or_buy_price - row_bull_r_s_t_r.buy_price) /
                 row_bull_r_s_t_r.buy_price * 100 <=
                 -p_mandatory_stop_loss_rate then
                v_do_sell_buy := 1;
              end if;
            exception
              when no_data_found then
                -- 说明股票在这一天没有交易记录
                DBMS_OUTPUT.put_line('股票【' || row_bull_r_s_t_r.stock_code ||
                                     '】在日期【' || p_date || '】没有交易记录');
            end;
          end if;
        
          -- 判断是否是死叉，如果不是死叉，则返回下一条记录
          -- MACD死叉
          if row_bull_r_s_t_r.filter_type = 1 then
            if SCOTT.fnc_judge_by_macd_dead_cross(p_date,
                                                  row_bull_r_s_t_r.stock_code) = 1 then
              v_do_sell_buy := 1;
            end if;
          end if;
        
          -- 收盘价死叉五日均线
          if row_bull_r_s_t_r.filter_type = 3 then
            if SCOTT.FNC_JUDGE_BY_C_P_DEAD_MA5(p_date,
                                               row_bull_r_s_t_r.stock_code) = 1 then
              v_do_sell_buy := 1;
            end if;
          end if;
        
          -- 平均K线是否从上涨趋势变为下跌趋势
          if row_bull_r_s_t_r.filter_type = 5 then
            if SCOTT.FNC_JUDGE_HEI_KIN_ASHI_DOWN_UP(p_date,
                                                    row_bull_r_s_t_r.stock_code) = 1 then
              v_do_sell_buy := 1;
            end if;
          end if;
        
          -- KD死叉
          if row_bull_r_s_t_r.filter_type = 7 then
            if SCOTT.fnc_judge_by_kd_dead_cross(p_date,
                                                row_bull_r_s_t_r.stock_code) = 1 then
              v_do_sell_buy := 1;
            end if;
          end if;
        
          -- 如果前一交易日收盘价大于布林带下轨，并且当前交易日收盘价小于前一交易日收盘价，则反弹结束
          if row_bull_r_s_t_r.filter_type = 9 then
            if SCOTT.fnc_judge_cp_up_end_f_b_boll(p_date,
                                                  row_bull_r_s_t_r.stock_code) = 1 then
              v_do_sell_buy := 1;
            end if;
          end if;
        
          if v_do_sell_buy = 1 then
            -- 如果是死叉，则卖出这支股票
            -- 查找日期为p_sell_date的某只股票的收盘价（卖出价格）
            begin
              select t1.close_price
                into v_sell_or_buy_price
                from stock_transaction_data_all t1
               where t1.code_ = row_bull_r_s_t_r.stock_code
                 and t1.date_ = to_date(p_date, 'yyyy-mm-dd');
            exception
              when no_data_found then
                -- 重置
                v_do_sell_buy := 0;
                -- 说明股票在这一天没有交易记录
                DBMS_OUTPUT.put_line('股票【' || row_bull_r_s_t_r.stock_code ||
                                     '】在日期【' || p_date || '】没有交易记录');
                continue;
            end;
          
            -- 更新robot3_stock_transact_record表的sell_date、sell_price和sell_amount字段
            update robot3_stock_transact_record t
               set t.sell_date   = to_date(p_date, 'yyyy-mm-dd'),
                   t.sell_price  = v_sell_or_buy_price,
                   t.sell_amount = t.buy_amount
             where t.id_ = row_bull_r_s_t_r.id_;
            commit;
            -- 更新robot3_stock_transact_record表的profit_and_loss和profit_and_loss_rate字段
            update robot3_stock_transact_record t
               set t.profit_and_loss     =
                   (t.sell_price * t.sell_amount) -
                   (t.buy_price * t.buy_amount),
                   t.profit_and_loss_rate =
                   (t.sell_price - t.buy_price) / t.buy_price * 100
             where t.id_ = row_bull_r_s_t_r.id_;
            commit;
          end if;
        
          -- 重置
          v_do_sell_buy := 0;
        end loop;
      
        -- 做空
        for row_short_r_s_t_r in cur_short_r_s_t_r loop
        
          -- 判断是否有强制止损
          if p_mandatory_stop_loss = 1 then
            begin
              select t1.close_price
                into v_sell_or_buy_price
                from stock_transaction_data_all t1
               where t1.code_ = row_short_r_s_t_r.stock_code
                 and t1.date_ = to_date(p_date, 'yyyy-mm-dd');
              if (v_sell_or_buy_price - row_short_r_s_t_r.sell_price) /
                 row_short_r_s_t_r.sell_price * 100 >
                 p_mandatory_stop_loss_rate then
                v_do_sell_buy := 1;
              end if;
            exception
              when no_data_found then
                -- 说明股票在这一天没有交易记录
                DBMS_OUTPUT.put_line('股票【' || row_short_r_s_t_r.stock_code ||
                                     '】在日期【' || p_date || '】没有交易记录');
            end;
          end if;
        
          -- 判断是否是金叉，如果不是金叉，则返回下一条记录
          -- MACD金叉
          if row_short_r_s_t_r.filter_type = 2 then
            if SCOTT.fnc_judge_by_macd_gold_cross(p_date,
                                                  row_short_r_s_t_r.stock_code) = 1 then
              v_do_sell_buy := 1;
            end if;
          end if;
        
          -- 收盘价金叉五日均线
          if row_short_r_s_t_r.filter_type = 4 then
            if SCOTT.FNC_JUDGE_BY_C_P_GOLD_MA5(p_date,
                                               row_short_r_s_t_r.stock_code) = 1 then
              v_do_sell_buy := 1;
            end if;
          end if;
        
          -- 平均K线是否从下跌趋势变为上涨趋势
          if row_short_r_s_t_r.filter_type = 6 then
            if SCOTT.FNC_JUDGE_HEI_KIN_ASHI_UP_DOWN(p_date,
                                                    row_short_r_s_t_r.stock_code) = 1 then
              v_do_sell_buy := 1;
            end if;
          end if;
        
          -- KD金叉
          if row_short_r_s_t_r.filter_type = 8 then
            if SCOTT.fnc_judge_by_kd_gold_cross(p_date,
                                                row_short_r_s_t_r.stock_code) = 1 then
              v_do_sell_buy := 1;
            end if;
          end if;
        
          -- 如果前一交易日收盘价小于布林带上轨，并且当前交易日收盘价大于前一交易日收盘价，则调整结束
          if row_short_r_s_t_r.filter_type = 10 then
            if SCOTT.fnc_judge_cp_dn_end_f_t_boll(p_date,
                                                  row_short_r_s_t_r.stock_code) = 1 then
              v_do_sell_buy := 1;
            end if;
          end if;
        
          if v_do_sell_buy = 1 then
            -- 如果是金叉，则买入这支股票
            -- 查找日期为p_date的某只股票的收盘价（卖出价格）
            begin
              select t1.close_price
                into v_sell_or_buy_price
                from stock_transaction_data_all t1
               where t1.code_ = row_short_r_s_t_r.stock_code
                 and t1.date_ = to_date(p_date, 'yyyy-mm-dd');
            exception
              when no_data_found then
                -- 重置
                v_do_sell_buy := 0;
                -- 说明股票在这一天没有交易记录
                DBMS_OUTPUT.put_line('股票【' || row_short_r_s_t_r.stock_code ||
                                     '】在日期【' || p_date || '】没有交易记录');
                continue;
            end;
          
            -- 更新robot3_stock_transact_record表的buy_date、buy_price和buy_amount字段
            update robot3_stock_transact_record t
               set t.buy_date   = to_date(p_date, 'yyyy-mm-dd'),
                   t.buy_price  = v_sell_or_buy_price,
                   t.buy_amount = t.sell_amount
             where t.id_ = row_short_r_s_t_r.id_;
            commit;
            -- 更新robot3_stock_transact_record表的profit_and_loss和profit_and_loss_rate字段
            update robot3_stock_transact_record t
               set t.profit_and_loss     =
                   (t.sell_price * t.sell_amount) -
                   (t.buy_price * t.buy_amount),
                   t.profit_and_loss_rate =
                   (t.sell_price - t.buy_price) / t.sell_price * 100
             where t.id_ = row_short_r_s_t_r.id_;
            commit;
          end if;
        
          -- 重置
          v_do_sell_buy := 0;
        end loop;
      end loop;
    end;
  end sell_or_buy;

  --------------------------------------------------------------------------------------------------

  -- 根据当日卖出/买入股票的收盘价，在卖股票/买股票之后，更新robot3_account表
  procedure update_robot_account_after_s_b(p_date in varchar2) as
  begin
    declare
      -- 机器人3账号
      v_robot_name varchar2(20);
      -- 股票资产
      v_stock_assets number;
      -- 卖出/买入股票的数量
      v_sell_or_buy_stock_number number;
      -- 股票的收盘价
      v_current_close_price number;
      -- 表robot_stock_transaction_record类型的记录
      row_robot_stock_tranc_record robot3_stock_transact_record%rowtype;
      -- 返回机器人账户
      cursor cur_robot_account is
        select * from robot3_account;
      -- 计算每一个机器人账户，在某一天卖完股票后的收益
      cursor cur_bull_stock is
        select *
          from robot3_stock_transact_record rstr
         where rstr.robot_name = v_robot_name
           and rstr.direction = 1
           and rstr.sell_date is null
           and rstr.sell_price is null
           and rstr.sell_amount is null;
      -- 计算每一个机器人账户，在某一天买完股票后的收益
      cursor cur_short_stock is
        select *
          from robot3_stock_transact_record rstr
         where rstr.robot_name = v_robot_name
           and rstr.direction = -1
           and rstr.buy_date is null
           and rstr.buy_price is null
           and rstr.buy_amount is null;
    begin
      ----------------------------------- 做多和做空 ---------------------------------
      for row_robot_account in cur_robot_account loop
        -- 机器人账户名称
        v_robot_name               := row_robot_account.robot_name;
        v_stock_assets             := 0;
        v_sell_or_buy_stock_number := 0;
        v_current_close_price      := null;
      
        -- 做多
        for row_robot_stock_tranc_record in cur_bull_stock loop
        
          -- 查找某只股票在某一天的收盘价
          begin
            select t.close_price
              into v_current_close_price
              from stock_transaction_data_all t
             where t.code_ = row_robot_stock_tranc_record.stock_code
               and t.date_ = to_date(p_date, 'yyyy-mm-dd');
          exception
            when no_data_found then
              v_current_close_price := null;
              -- 说明股票在这一天没有交易记录
              DBMS_OUTPUT.put_line('股票【' ||
                                   row_robot_stock_tranc_record.stock_code ||
                                   '】在日期【' || p_date || '】没有交易记录');
          end;
          -- 如果在某一天没有收盘价，比如停牌，则查找最近一个交易日的收盘价
          if v_current_close_price is null then
            select std.close_price
              into v_current_close_price
              from (select *
                      from stock_transaction_data_all t
                     where t.code_ = row_robot_stock_tranc_record.stock_code
                       and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                     order by t.date_ desc) std
             where rownum <= 1;
          end if;
        
          -- 卖完股票后的收益
          v_stock_assets             := v_stock_assets +
                                        v_current_close_price *
                                        row_robot_stock_tranc_record.buy_amount;
          v_sell_or_buy_stock_number := v_sell_or_buy_stock_number + 1;
        end loop;
      
        -- 做空
        for row_robot_stock_tranc_record in cur_short_stock loop
        
          -- 查找某只股票在某一天的收盘价
          begin
            select t.close_price
              into v_current_close_price
              from stock_transaction_data_all t
             where t.code_ = row_robot_stock_tranc_record.stock_code
               and t.date_ = to_date(p_date, 'yyyy-mm-dd');
          exception
            when no_data_found then
              v_current_close_price := null;
              -- 说明股票在这一天没有交易记录
              DBMS_OUTPUT.put_line('股票【' ||
                                   row_robot_stock_tranc_record.stock_code ||
                                   '】在日期【' || p_date || '】没有交易记录');
          end;
          -- 如果在某一天没有收盘价，比如停牌，则查找最近一个交易日的收盘价
          if v_current_close_price is null then
            select std.close_price
              into v_current_close_price
              from (select *
                      from stock_transaction_data_all t
                     where t.code_ = row_robot_stock_tranc_record.stock_code
                       and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                     order by t.date_ desc) std
             where rownum <= 1;
          end if;
        
          -- 买完股票后的收益
          v_stock_assets             := v_stock_assets +
                                        (row_robot_stock_tranc_record.sell_price -
                                        v_current_close_price) *
                                        row_robot_stock_tranc_record.sell_amount +
                                        row_robot_stock_tranc_record.sell_price *
                                        row_robot_stock_tranc_record.sell_amount;
          v_sell_or_buy_stock_number := v_sell_or_buy_stock_number + 1;
        end loop;
      
        -- 更新robot3_account表
        update robot3_account t
           set t.hold_stock_number = v_sell_or_buy_stock_number,
               t.stock_assets      = v_stock_assets,
               t.capital_assets    = t.total_assets - v_stock_assets
         where t.robot_name = v_robot_name;
        commit;
      end loop;
    end;
  end update_robot_account_after_s_b;

  --------------------------------------------------------------------------------------------------

  -- 过滤条件：MACD金叉
  procedure filter_by_macd_gold_cross(p_date in varchar2) as
  begin
    declare
      -- 股票代码
      v_code varchar2(10);
      -- 当前dif
      v_current_dif number;
      -- 当前dea
      v_current_dea number;
      -- 前一日dif
      v_previous_dif number;
      -- 前一日dea
      v_previous_dea number;
      -- 记录数量
      v_record_number number;
      -- 查询robot3_stock_filter表的全部记录
      cursor cur_robot_stock_filter is
        select * from robot3_stock_filter;
    begin
      for row_robot_stock_filter in cur_robot_stock_filter loop
        v_code := row_robot_stock_filter.stock_code;
      
        -- 当前dif、dea
        select count(*)
          into v_record_number
          from stock_transaction_data_all t
         where t.code_ = v_code
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
        if v_record_number = 0 then
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
          continue;
        end if;
        select t.dif, t.dea
          into v_current_dif, v_current_dea
          from stock_transaction_data_all t
         where t.code_ = v_code
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
      
        -- 前一日dif、dea
        select count(*)
          into v_record_number
          from (select *
                  from stock_transaction_data_all t
                 where t.code_ = v_code
                   and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc) b
         where rownum <= 1;
        if v_record_number = 0 then
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
          continue;
        end if;
        select b.dif, b.dea
          into v_previous_dif, v_previous_dea
          from (select *
                  from stock_transaction_data_all t
                 where t.code_ = v_code
                   and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc) b
         where rownum <= 1;
      
        -- 如果不是金叉，则从robot3_stock_filter表中删除这只股票
        if v_current_dif >= v_current_dea and
           v_previous_dif <= v_previous_dea then
          continue;
        else
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
        end if;
      
      end loop;
    end;
  end filter_by_macd_gold_cross;

  --------------------------------------------------------------------------------------------------

  -- 过滤条件：MACD死叉
  procedure filter_by_macd_dead_cross(p_date in varchar2) as
  begin
    declare
      -- 股票代码
      v_code varchar2(10);
      -- 当前dif
      v_current_dif number;
      -- 当前dea
      v_current_dea number;
      -- 前一日dif
      v_previous_dif number;
      -- 前一日dea
      v_previous_dea number;
      -- 记录数量
      v_record_number number;
      -- 查询robot3_stock_filter表的全部记录
      cursor cur_robot_stock_filter is
        select * from robot3_stock_filter;
    begin
      for row_robot_stock_filter in cur_robot_stock_filter loop
        v_code := row_robot_stock_filter.stock_code;
      
        -- 当前dif、dea
        select count(*)
          into v_record_number
          from stock_transaction_data_all t
         where t.code_ = v_code
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
        if v_record_number = 0 then
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
          continue;
        end if;
        select t.dif, t.dea
          into v_current_dif, v_current_dea
          from stock_transaction_data_all t
         where t.code_ = v_code
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
      
        -- 前一日dif、dea
        select count(*)
          into v_record_number
          from (select *
                  from stock_transaction_data_all t
                 where t.code_ = v_code
                   and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc) b
         where rownum <= 1;
        if v_record_number = 0 then
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
          continue;
        end if;
        select b.dif, b.dea
          into v_previous_dif, v_previous_dea
          from (select *
                  from stock_transaction_data_all t
                 where t.code_ = v_code
                   and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc) b
         where rownum <= 1;
      
        -- 如果不是死叉，则从robot3_stock_filter表中删除这只股票
        if v_current_dif <= v_current_dea and
           v_previous_dif >= v_previous_dea then
          continue;
        else
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
        end if;
      
      end loop;
    end;
  end filter_by_macd_dead_cross;

  --------------------------------------------------------------------------------------------------

  -- 过滤条件：删除除过权的股票
  procedure filter_by_xr(p_begin_date in varchar2, p_end_date in varchar2) as
  begin
    declare
      -- 股票代码
      v_code varchar2(10);
      -- 查询robot3_stock_filter表的全部记录
      cursor cur_robot_stock_filter is
        select * from robot3_stock_filter;
    begin
      for cur_rsf in cur_robot_stock_filter loop
        v_code := cur_rsf.stock_code;
      
        -- 判断这只股票在指定时间范围内是否出现了除权
        if fnc_stock_xr(v_code, p_begin_date, p_end_date) = 1 then
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
        end if;
      end loop;
    end;
  end filter_by_xr;

  --------------------------------------------------------------------------------------------------

  -- 过滤条件：周线级别KD金叉
  procedure filter_by_week_kd_gold_cross(p_date in varchar2) as
  begin
    declare
      -- 股票代码
      v_code varchar2(10);
      -- 股票数量
      v_num number;
      -- 查询robot3_stock_filter表的全部记录
      cursor cur_robot_stock_filter is
        select * from robot3_stock_filter;
    begin
      for cur_rsf in cur_robot_stock_filter loop
        v_code := cur_rsf.stock_code;
        v_num  := 0;
      
        -- 判断这只股票在周线级别KD是否出现了金叉
        select count(*)
          into v_num
          from stock_week t
         where t.code_ = v_code
           and t.begin_date <= p_date
           and t.end_date >= p_date
           and t.k >= t.d;
      
        if v_num = 0 then
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
        end if;
      end loop;
    end;
  end filter_by_week_kd_gold_cross;

  --------------------------------------------------------------------------------------------------

  -- 过滤条件：当前收盘价与某段时间最高价的百分比。p_begin_date为计算最高价的开始时间，通常为上一个牛市。
  -- filter_by_percentage_of_current_close_price_compare_to_some_time_highest_price
  procedure filter_p_o_c_c_p_c_t_s_t_h_p(p_begin_date in varchar2,
                                         p_date       in varchar2,
                                         p_percentage in number) as
  begin
    declare
      -- 股票代码
      v_code varchar2(10);
      -- 当前收盘价
      v_current_close_price number;
      -- 某段时间最高价
      v_history_highest_price number;
      -- 记录数
      v_record_number number;
      -- 查询robot3_stock_filter表的全部记录
      cursor cur_robot_stock_filter is
        select * from robot3_stock_filter;
    begin
      for cur_rsf in cur_robot_stock_filter loop
        v_code := cur_rsf.stock_code;
      
        -- 判断这只股票在指定时间范围内是否出现了除权
        /*if fnc_stock_xr(v_code, p_begin_date, p_date) = 1 then
          delete from robot_stock_filter t where t.stock_code = v_code;
          commit;
          continue;
        end if;*/
      
        -- 当前收盘价
        select count(*)
          into v_record_number
          from stock_transaction_data_all t
         where t.code_ = v_code
           and t.date_ >= to_date(p_begin_date, 'yyyy-mm-dd')
           and t.date_ <= to_date(p_date, 'yyyy-mm-dd');
      
        if v_record_number = 0 then
          continue;
        end if;
      
        select *
          into v_current_close_price
          from (select t.close_price
                  from stock_transaction_data_all t
                 where t.code_ = v_code
                   and t.date_ >= to_date(p_begin_date, 'yyyy-mm-dd')
                   and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc)
         where rownum <= 1;
      
        -- 某段时间最高价
        select count(*)
          into v_record_number
          from stock_transaction_data_all t
         where t.code_ = v_code
           and t.date_ >= to_date(p_begin_date, 'yyyy-mm-dd')
           and t.date_ <= to_date(p_date, 'yyyy-mm-dd');
      
        if v_record_number = 0 then
          continue;
        end if;
      
        select max(t.highest_price)
          into v_history_highest_price
          from stock_transaction_data_all t
         where t.code_ = v_code
           and t.date_ >= to_date(p_begin_date, 'yyyy-mm-dd')
           and t.date_ <= to_date(p_date, 'yyyy-mm-dd');
      
        -- 如果当前收盘价不是某段时间最高价的百分之p_percentage，则从robot3_stock_filter表中删除
        if (v_current_close_price / v_history_highest_price * 100) <=
           p_percentage then
          continue;
        else
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
        end if;
      
      end loop;
    end;
  end filter_p_o_c_c_p_c_t_s_t_h_p;

  --------------------------------------------------------------------------------------------------

  -- 过滤条件：当前收盘价与某段时间最低价的百分比。p_begin_date为计算最高价的开始时间，通常为上一个熊市。
  -- filter_by_percentage_of_current_close_price_compare_to_some_time_lowest_price
  procedure filter_p_o_c_c_p_c_t_s_t_l_p(p_begin_date in varchar2,
                                         p_date       in varchar2,
                                         p_percentage in number) as
  begin
    declare
      -- 股票代码
      v_code varchar2(10);
      -- 当前收盘价
      v_current_close_price number;
      -- 某段时间最高价
      v_history_lowest_price number;
      -- 记录数
      v_record_number number;
      -- 查询robot3_stock_filter表的全部记录
      cursor cur_robot_stock_filter is
        select * from robot3_stock_filter;
    begin
      for cur_rsf in cur_robot_stock_filter loop
        v_code := cur_rsf.stock_code;
      
        -- 判断这只股票在指定时间范围内是否出现了除权
        /*if fnc_stock_xr(v_code, p_begin_date, p_date) = 1 then
          delete from robot_stock_filter t where t.stock_code = v_code;
          commit;
          continue;
        end if;*/
      
        -- 当前收盘价
        select count(*)
          into v_record_number
          from stock_transaction_data_all t
         where t.code_ = v_code
           and t.date_ >= to_date(p_begin_date, 'yyyy-mm-dd')
           and t.date_ <= to_date(p_date, 'yyyy-mm-dd');
      
        if v_record_number = 0 then
          continue;
        end if;
      
        select *
          into v_current_close_price
          from (select t.close_price
                  from stock_transaction_data_all t
                 where t.code_ = v_code
                   and t.date_ >= to_date(p_begin_date, 'yyyy-mm-dd')
                   and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc)
         where rownum <= 1;
      
        -- 某段时间最低价
        select count(*)
          into v_record_number
          from stock_transaction_data_all t
         where t.code_ = v_code
           and t.date_ >= to_date(p_begin_date, 'yyyy-mm-dd')
           and t.date_ <= to_date(p_date, 'yyyy-mm-dd');
      
        if v_record_number = 0 then
          continue;
        end if;
      
        select min(t.lowest_price)
          into v_history_lowest_price
          from stock_transaction_data_all t
         where t.code_ = v_code
           and t.date_ >= to_date(p_begin_date, 'yyyy-mm-dd')
           and t.date_ <= to_date(p_date, 'yyyy-mm-dd');
      
        -- 如果当前收盘价不是某段时间最低价的百分之p_percentage，则从robot3_stock_filter表中删除
        if (v_current_close_price / v_history_lowest_price * 100) >=
           p_percentage then
          continue;
        else
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
        end if;
      
      end loop;
    end;
  end filter_p_o_c_c_p_c_t_s_t_l_p;

  --------------------------------------------------------------------------------------------------

  -- 按照条件：MA不单调递减，过滤股票
  procedure filter_by_ma_not_decreasing(p_ma_level in number,
                                        p_date     in varchar2,
                                        p_rownum   in number) as
  begin
    declare
      -- 股票代码
      v_code varchar2(10);
      -- 下一日的5日均线
      v_next_ma5 number;
      -- 下一日的10日均线
      v_next_ma10 number;
      -- 下一日的20日均线
      v_next_ma20 number;
      -- 下一日的60日均线
      v_next_ma60 number;
      -- 下一日的120日均线
      v_next_ma120 number;
      -- 下一日的250日均线
      v_next_ma250 number;
      -- 表示是否是单调不递减，-1表示否，1表示是
      v_increasing number := 1;
      -- 查询robot3_stock_filter表的全部记录
      cursor cur_robot_stock_filter is
        select * from robot3_stock_filter;
      -- 根据条件code、date，查找stock_transaction_data_all表，按降序排列，只取前rownum条记录
      cursor cur_find_c_a_d_o_d_d_ltq_rn is
        select *
          from (select *
                  from stock_transaction_data_all t
                 where t.code_ = v_code
                   and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc) b
         where rownum <= p_rownum;
    begin
      for cur_rsf in cur_robot_stock_filter loop
        v_code := cur_rsf.stock_code;
      
        -- 重新设置为1
        v_increasing := 1;
      
        for cur_fbcadobddlr in cur_find_c_a_d_o_d_d_ltq_rn loop
        
          -- 5日均线
          if p_ma_level = 5 then
            begin
              -- 返回游标cur_find_by_code_and_date_order_by_date_desc_ltq_rownum下一条记录的ma5字段
              select b.ma5
                into v_next_ma5
                from (select *
                        from stock_transaction_data_all t
                       where t.code_ = v_code
                         and t.date_ < cur_fbcadobddlr.date_
                       order by t.date_ desc) b
               where rownum <= 1;
            
              -- 如果出现了递减的情况，则直接跳出这个循环
              if v_next_ma5 is null or v_next_ma5 > cur_fbcadobddlr.ma5 then
                v_increasing := -1;
                exit;
              end if;
            exception
              when no_data_found then
                -- 如果均线数量不足p_rownum个，则也认为是不满足不单调递减条件
                v_increasing := -1;
                DBMS_OUTPUT.put_line('判断5日均线是否不单调递减时，股票' || v_code ||
                                     '均线数量不足' || p_rownum ||
                                     '个，则也认为是不满足不单调递减条件');
                exit;
            end;
          
            -- 为了下一步的迭代
            -- v_current_ma5 := cur_fbcadobddlr.ma5;
          end if;
        
          -- 10日均线
          if p_ma_level = 10 then
            begin
              -- 返回游标cur_find_by_code_and_date_order_by_date_desc_ltq_rownum下一条记录的ma10字段
              select b.ma10
                into v_next_ma10
                from (select *
                        from stock_transaction_data_all t
                       where t.code_ = v_code
                         and t.date_ < cur_fbcadobddlr.date_
                       order by t.date_ desc) b
               where rownum <= 1;
            
              -- 如果出现了递减的情况，则直接跳出这个循环
              if v_next_ma10 is null or v_next_ma10 > cur_fbcadobddlr.ma10 then
                v_increasing := -1;
                exit;
              end if;
            exception
              when no_data_found then
                -- 如果均线数量不足p_rownum个，则也认为是不满足不单调递减条件
                v_increasing := -1;
                DBMS_OUTPUT.put_line('判断10日均线是否不单调递减时，股票' || v_code ||
                                     '均线数量不足' || p_rownum ||
                                     '个，则也认为是不满足不单调递减条件');
                exit;
            end;
          
            -- 为了下一步的迭代
            -- v_current_ma10 := cur_fbcadobddlr.ma10;
          end if;
        
          -- 20日均线
          if p_ma_level = 20 then
            begin
              -- 返回游标cur_find_by_code_and_date_order_by_date_desc_ltq_rownum下一条记录的ma20字段
              select b.ma20
                into v_next_ma20
                from (select *
                        from stock_transaction_data_all t
                       where t.code_ = v_code
                         and t.date_ < cur_fbcadobddlr.date_
                       order by t.date_ desc) b
               where rownum <= 1;
            
              -- 如果出现了递减的情况，则直接跳出这个循环
              if v_next_ma20 is null or v_next_ma20 > cur_fbcadobddlr.ma20 then
                v_increasing := -1;
                exit;
              end if;
            exception
              when no_data_found then
                -- 如果均线数量不足p_rownum个，则也认为是不满足不单调递减条件
                v_increasing := -1;
                DBMS_OUTPUT.put_line('判断20日均线是否不单调递减时，股票' || v_code ||
                                     '均线数量不足' || p_rownum ||
                                     '个，则也认为是不满足不单调递减条件');
                exit;
            end;
          
            -- 为了下一步的迭代
            -- v_current_ma20 := cur_fbcadobddlr.ma20;
          end if;
        
          -- 60日均线
          if p_ma_level = 60 then
            begin
              -- 返回游标cur_find_by_code_and_date_order_by_date_desc_ltq_rownum下一条记录的ma60字段
              select b.ma60
                into v_next_ma60
                from (select *
                        from stock_transaction_data_all t
                       where t.code_ = v_code
                         and t.date_ < cur_fbcadobddlr.date_
                       order by t.date_ desc) b
               where rownum <= 1;
            
              -- 如果出现了递减的情况，则直接跳出这个循环
              if v_next_ma60 is null or v_next_ma60 > cur_fbcadobddlr.ma60 then
                v_increasing := -1;
                exit;
              end if;
            exception
              when no_data_found then
                -- 如果均线数量不足p_rownum个，则也认为是不满足不单调递减条件
                v_increasing := -1;
                DBMS_OUTPUT.put_line('判断60日均线是否不单调递减时，股票' || v_code ||
                                     '均线数量不足' || p_rownum ||
                                     '个，则也认为是不满足不单调递减条件');
                exit;
            end;
          
            -- 为了下一步的迭代
            -- v_current_ma60 := cur_fbcadobddlr.ma60;
          end if;
        
          -- 120日均线
          if p_ma_level = 120 then
            begin
              -- 返回游标cur_find_by_code_and_date_order_by_date_desc_ltq_rownum下一条记录的ma120字段
              select b.ma120
                into v_next_ma120
                from (select *
                        from stock_transaction_data_all t
                       where t.code_ = v_code
                         and t.date_ < cur_fbcadobddlr.date_
                       order by t.date_ desc) b
               where rownum <= 1;
            
              -- 如果出现了递减的情况，则直接跳出这个循环
              if v_next_ma120 is null or
                 v_next_ma120 > cur_fbcadobddlr.ma120 then
                v_increasing := -1;
                exit;
              end if;
            exception
              when no_data_found then
                -- 如果均线数量不足p_rownum个，则也认为是不满足不单调递减条件
                v_increasing := -1;
                DBMS_OUTPUT.put_line('判断120日均线是否不单调递减时，股票' || v_code ||
                                     '均线数量不足' || p_rownum ||
                                     '个，则也认为是不满足不单调递减条件');
                exit;
            end;
          
            -- 为了下一步的迭代
            -- v_current_ma120 := cur_fbcadobddlr.ma120;
          end if;
        
          -- 250日均线
          if p_ma_level = 250 then
            begin
              -- 返回游标cur_find_by_code_and_date_order_by_date_desc_ltq_rownum下一条记录的ma250字段
              select b.ma250
                into v_next_ma250
                from (select *
                        from stock_transaction_data_all t
                       where t.code_ = v_code
                         and t.date_ < cur_fbcadobddlr.date_
                       order by t.date_ desc) b
               where rownum <= 1;
            
              -- 如果出现了递减的情况，则直接跳出这个循环
              if v_next_ma250 is null or
                 v_next_ma250 > cur_fbcadobddlr.ma250 then
                v_increasing := -1;
                exit;
              end if;
            exception
              when no_data_found then
                -- 如果均线数量不足p_rownum个，则也认为是不满足不单调递减条件
                v_increasing := -1;
                DBMS_OUTPUT.put_line('判断250日均线是否不单调递减时，股票' || v_code ||
                                     '均线数量不足' || p_rownum ||
                                     '个，则也认为是不满足不单调递减条件');
                exit;
            end;
          
            -- 为了下一步的迭代
            -- v_current_ma250 := cur_fbcadobddlr.ma250;
          end if;
        
        end loop;
      
        -- 如果出现了递减的情况，则将这个股票从robot3_stock_filter表中删除
        if v_increasing = -1 then
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
        end if;
      end loop;
    end;
  end filter_by_ma_not_decreasing;

  --------------------------------------------------------------------------------------------------

  -- 按照条件：MA不单调递增，过滤股票
  procedure filter_by_ma_not_increasing(p_ma_level in number,
                                        p_date     in varchar2,
                                        p_rownum   in number) as
  begin
    declare
      -- 股票代码
      v_code varchar2(10);
      -- 下一日的5日均线
      v_next_ma5 number;
      -- 下一日的10日均线
      v_next_ma10 number;
      -- 下一日的20日均线
      v_next_ma20 number;
      -- 下一日的60日均线
      v_next_ma60 number;
      -- 下一日的120日均线
      v_next_ma120 number;
      -- 下一日的250日均线
      v_next_ma250 number;
      -- 表示是否是单调不递增，-1表示否，1表示是
      v_decreasing number := 1;
      -- 查询robot3_stock_filter表的全部记录
      cursor cur_robot_stock_filter is
        select * from robot3_stock_filter;
      -- 根据条件code、date，查找stock_transaction_data_all表，按降序排列，只取前rownum条记录
      -- 油表名称cur_find_by_code_and_date_order_by_date_desc_ltq_rownum
      cursor cur_find_c_a_d_o_d_d_ltq_rn is
        select *
          from (select *
                  from stock_transaction_data_all t
                 where t.code_ = v_code
                   and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc) b
         where rownum <= p_rownum;
    begin
      for cur_rsf in cur_robot_stock_filter loop
        v_code := cur_rsf.stock_code;
      
        -- 重新设置为1
        v_decreasing := 1;
      
        for cur_fbcadobddlr in cur_find_c_a_d_o_d_d_ltq_rn loop
        
          -- 5日均线
          if p_ma_level = 5 then
            begin
              -- 返回游标cur_find_by_code_and_date_order_by_date_desc_ltq_rownum下一条记录的ma5字段
              select b.ma5
                into v_next_ma5
                from (select *
                        from stock_transaction_data_all t
                       where t.code_ = v_code
                         and t.date_ < cur_fbcadobddlr.date_
                       order by t.date_ desc) b
               where rownum <= 1;
            
              -- 如果出现了递增的情况，则直接跳出这个循环
              if v_next_ma5 is null or v_next_ma5 < cur_fbcadobddlr.ma5 then
                v_decreasing := -1;
                exit;
              end if;
            exception
              when no_data_found then
                -- 如果均线数量不足p_rownum个，则也认为是不满足不单调递增条件
                v_decreasing := -1;
                DBMS_OUTPUT.put_line('判断5日均线是否不单调递增时，股票' || v_code ||
                                     '均线数量不足' || p_rownum ||
                                     '个，则也认为是不满足不单调递增条件');
                exit;
            end;
          
            -- 为了下一步的迭代
            -- v_current_ma5 := cur_fbcadobddlr.ma5;
          end if;
        
          -- 10日均线
          if p_ma_level = 10 then
            begin
              -- 返回游标cur_find_by_code_and_date_order_by_date_desc_ltq_rownum下一条记录的ma10字段
              select b.ma10
                into v_next_ma10
                from (select *
                        from stock_transaction_data_all t
                       where t.code_ = v_code
                         and t.date_ < cur_fbcadobddlr.date_
                       order by t.date_ desc) b
               where rownum <= 1;
            
              -- 如果出现了递增的情况，则直接跳出这个循环
              if v_next_ma10 is null or v_next_ma10 < cur_fbcadobddlr.ma10 then
                v_decreasing := -1;
                exit;
              end if;
            exception
              when no_data_found then
                -- 如果均线数量不足p_rownum个，则也认为是不满足不单调递增条件
                v_decreasing := -1;
                DBMS_OUTPUT.put_line('判断10日均线是否不单调递增时，股票' || v_code ||
                                     '均线数量不足' || p_rownum ||
                                     '个，则也认为是不满足不单调递增条件');
                exit;
            end;
          
            -- 为了下一步的迭代
            -- v_current_ma10 := cur_fbcadobddlr.ma10;
          end if;
        
          -- 20日均线
          if p_ma_level = 20 then
            begin
              -- 返回游标cur_find_by_code_and_date_order_by_date_desc_ltq_rownum下一条记录的ma20字段
              select b.ma20
                into v_next_ma20
                from (select *
                        from stock_transaction_data_all t
                       where t.code_ = v_code
                         and t.date_ < cur_fbcadobddlr.date_
                       order by t.date_ desc) b
               where rownum <= 1;
            
              -- 如果出现了递增的情况，则直接跳出这个循环
              if v_next_ma20 is null or v_next_ma20 < cur_fbcadobddlr.ma20 then
                v_decreasing := -1;
                exit;
              end if;
            exception
              when no_data_found then
                -- 如果均线数量不足p_rownum个，则也认为是不满足不单调递增条件
                v_decreasing := -1;
                DBMS_OUTPUT.put_line('判断20日均线是否不单调递增时，股票' || v_code ||
                                     '均线数量不足' || p_rownum ||
                                     '个，则也认为是不满足不单调递增条件');
                exit;
            end;
          
            -- 为了下一步的迭代
            -- v_current_ma20 := cur_fbcadobddlr.ma20;
          end if;
        
          -- 60日均线
          if p_ma_level = 60 then
            begin
              -- 返回游标cur_find_by_code_and_date_order_by_date_desc_ltq_rownum下一条记录的ma60字段
              select b.ma60
                into v_next_ma60
                from (select *
                        from stock_transaction_data_all t
                       where t.code_ = v_code
                         and t.date_ < cur_fbcadobddlr.date_
                       order by t.date_ desc) b
               where rownum <= 1;
            
              -- 如果出现了递增的情况，则直接跳出这个循环
              if v_next_ma60 is null or v_next_ma60 < cur_fbcadobddlr.ma60 then
                v_decreasing := -1;
                exit;
              end if;
            exception
              when no_data_found then
                -- 如果均线数量不足p_rownum个，则也认为是不满足不单调递增条件
                v_decreasing := -1;
                DBMS_OUTPUT.put_line('判断60日均线是否不单调递增时，股票' || v_code ||
                                     '均线数量不足' || p_rownum ||
                                     '个，则也认为是不满足不单调递增条件');
                exit;
            end;
          
            -- 为了下一步的迭代
            -- v_current_ma60 := cur_fbcadobddlr.ma60;
          end if;
        
          -- 120日均线
          if p_ma_level = 120 then
            begin
              -- 返回游标cur_find_by_code_and_date_order_by_date_desc_ltq_rownum下一条记录的ma120字段
              select b.ma120
                into v_next_ma120
                from (select *
                        from stock_transaction_data_all t
                       where t.code_ = v_code
                         and t.date_ < cur_fbcadobddlr.date_
                       order by t.date_ desc) b
               where rownum <= 1;
            
              -- 如果出现了递增的情况，则直接跳出这个循环
              if v_next_ma120 is null or
                 v_next_ma120 < cur_fbcadobddlr.ma120 then
                v_decreasing := -1;
                exit;
              end if;
            exception
              when no_data_found then
                -- 如果均线数量不足p_rownum个，则也认为是不满足不单调递增条件
                v_decreasing := -1;
                DBMS_OUTPUT.put_line('判断120日均线是否不单调递增时，股票' || v_code ||
                                     '均线数量不足' || p_rownum ||
                                     '个，则也认为是不满足不单调递增条件');
                exit;
            end;
          
            -- 为了下一步的迭代
            -- v_current_ma120 := cur_fbcadobddlr.ma120;
          end if;
        
          -- 250日均线
          if p_ma_level = 250 then
            begin
              -- 返回游标cur_find_by_code_and_date_order_by_date_desc_ltq_rownum下一条记录的ma250字段
              select b.ma250
                into v_next_ma250
                from (select *
                        from stock_transaction_data_all t
                       where t.code_ = v_code
                         and t.date_ < cur_fbcadobddlr.date_
                       order by t.date_ desc) b
               where rownum <= 1;
            
              -- 如果出现了递增的情况，则直接跳出这个循环
              if v_next_ma250 is null or
                 v_next_ma250 < cur_fbcadobddlr.ma250 then
                v_decreasing := -1;
                exit;
              end if;
            exception
              when no_data_found then
                -- 如果均线数量不足p_rownum个，则也认为是不满足不单调递增条件
                v_decreasing := -1;
                DBMS_OUTPUT.put_line('判断250日均线是否不单调递增时，股票' || v_code ||
                                     '均线数量不足' || p_rownum ||
                                     '个，则也认为是不满足不单调递增条件');
                exit;
            end;
          
            -- 为了下一步的迭代
            -- v_current_ma250 := cur_fbcadobddlr.ma250;
          end if;
        
        end loop;
      
        -- 如果出现了递增的情况，则将这个股票从robot3_stock_filter表中删除
        if v_decreasing = -1 then
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
        end if;
      end loop;
    end;
  end filter_by_ma_not_increasing;

  --------------------------------------------------------------------------------------------------

  -- 过滤条件：收盘价金叉五日均线
  procedure filter_by_c_p_g_c_ma5(p_date in varchar2) as
  begin
    declare
      -- 当前日期的收盘价
      v_current_close_price number;
      -- 当前日期的五日均线
      v_current_ma5 number;
      -- 前一日的收盘价
      v_previous_close_price number;
      -- 前一日的五日均线
      v_previous_ma5 number;
      -- robot3_account表的所有记录
      cursor cur_robot_stock_filter is
        select * from robot3_stock_filter;
    begin
      for row_robot_stock_filter in cur_robot_stock_filter loop
        -- 当日的收盘价和MA5
        begin
          select std.close_price, std.ma5
            into v_current_close_price, v_current_ma5
            from stock_transaction_data_all std
            join robot3_stock_filter rsf
              on rsf.stock_code = std.code_
             and std.code_ = row_robot_stock_filter.stock_code
             and std.date_ = to_date(p_date, 'yyyy-mm-dd');
        exception
          when no_data_found then
            DBMS_OUTPUT.put_line('代码为【' ||
                                 row_robot_stock_filter.stock_code ||
                                 '】的股票，
            在【' || p_date || '】没有收盘价和ma5');
            continue;
        end;
      
        -- 前一日的收盘价和ma5
        begin
          select std.close_price, std.ma5
            into v_previous_close_price, v_previous_ma5
            from stock_transaction_data_all std
            join robot3_stock_filter rsf
              on rsf.stock_code = std.code_
             and std.code_ = row_robot_stock_filter.stock_code
             and std.date_ =
                 (select b.date_
                    from (select *
                            from stock_transaction_data_all std2
                           where std2.date_ < to_date(p_date, 'yyyy-mm-dd')
                           order by std2.date_ desc) b
                   where rownum <= 1);
        exception
          when no_data_found then
            DBMS_OUTPUT.put_line('代码为【' ||
                                 row_robot_stock_filter.stock_code ||
                                 '】的股票，
            在【' || p_date ||
                                 '】的前一日没有收盘价和ma5');
            continue;
        end;
      
        -- 判断收盘价金叉五日均线是否成立
        if v_current_close_price >= v_current_ma5 and
           v_previous_close_price <= v_previous_ma5 then
          DBMS_OUTPUT.put_line('代码为【' || row_robot_stock_filter.stock_code ||
                               '】的股票，
            在【' || p_date ||
                               '】日，收盘价金叉五日均线成立');
        else
          delete from robot3_stock_filter t
           where t.stock_code = row_robot_stock_filter.stock_code;
          commit;
        end if;
      end loop;
    
    end;
  end filter_by_c_p_g_c_ma5;

  --------------------------------------------------------------------------------------------------

  -- 过滤条件：收盘价死叉五日均线。用于做空
  procedure filter_by_c_p_d_c_ma5(p_date in varchar2) as
  begin
    declare
      -- 当前日期的收盘价
      v_current_close_price number;
      -- 当前日期的五日均线
      v_current_ma5 number;
      -- 前一日的收盘价
      v_previous_close_price number;
      -- 前一日的五日均线
      v_previous_ma5 number;
      -- robot3_account表的所有记录
      cursor cur_robot_stock_filter is
        select * from robot3_stock_filter;
    begin
      for row_robot_stock_filter in cur_robot_stock_filter loop
        -- 当日的收盘价和MA5
        begin
          select std.close_price, std.ma5
            into v_current_close_price, v_current_ma5
            from stock_transaction_data_all std
            join robot3_stock_filter rsf
              on rsf.stock_code = std.code_
             and std.code_ = row_robot_stock_filter.stock_code
             and std.date_ = to_date(p_date, 'yyyy-mm-dd');
        exception
          when no_data_found then
            DBMS_OUTPUT.put_line('代码为【' ||
                                 row_robot_stock_filter.stock_code ||
                                 '】的股票，
            在【' || p_date || '】没有收盘价和ma5');
            continue;
        end;
      
        -- 前一日的收盘价和ma5
        begin
          select std.close_price, std.ma5
            into v_previous_close_price, v_previous_ma5
            from stock_transaction_data_all std
            join robot3_stock_filter rsf
              on rsf.stock_code = std.code_
             and std.code_ = row_robot_stock_filter.stock_code
             and std.date_ =
                 (select b.date_
                    from (select *
                            from stock_transaction_data_all std2
                           where std2.date_ < to_date(p_date, 'yyyy-mm-dd')
                           order by std2.date_ desc) b
                   where rownum <= 1);
        exception
          when no_data_found then
            DBMS_OUTPUT.put_line('代码为【' ||
                                 row_robot_stock_filter.stock_code ||
                                 '】的股票，
            在【' || p_date ||
                                 '】的前一日没有收盘价和ma5');
            continue;
        end;
      
        -- 判断收盘价死叉五日均线是否成立
        if v_current_close_price <= v_current_ma5 and
           v_previous_close_price >= v_previous_ma5 then
          DBMS_OUTPUT.put_line('代码为【' || row_robot_stock_filter.stock_code ||
                               '】的股票，
            在【' || p_date ||
                               '】日，收盘价死叉五日均线成立');
        else
          delete from robot3_stock_filter t
           where t.stock_code = row_robot_stock_filter.stock_code;
          commit;
        end if;
      end loop;
    
    end;
  end filter_by_c_p_d_c_ma5;

  --------------------------------------------------------------------------------------------------

  -- 过滤条件：KD金叉
  procedure filter_by_kd_gold_cross(p_date in varchar2) as
  begin
    declare
      -- 股票代码
      v_code varchar2(10);
      -- 当前k
      v_current_k number;
      -- 当前dea
      v_current_d number;
      -- 前一日k
      v_previous_k number;
      -- 前一日d
      v_previous_d number;
      -- 记录数量
      v_record_number number;
      -- 查询robot3_stock_filter表的全部记录
      cursor cur_robot_stock_filter is
        select * from robot3_stock_filter;
    begin
      for row_robot_stock_filter in cur_robot_stock_filter loop
        v_code := row_robot_stock_filter.stock_code;
      
        -- 当前k、d
        select count(*)
          into v_record_number
          from stock_transaction_data_all t
         where t.code_ = v_code
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
        if v_record_number = 0 then
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
          continue;
        end if;
        select t.k, t.d
          into v_current_k, v_current_d
          from stock_transaction_data_all t
         where t.code_ = v_code
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
      
        -- 前一日k、d
        select count(*)
          into v_record_number
          from (select *
                  from stock_transaction_data_all t
                 where t.code_ = v_code
                   and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc) b
         where rownum <= 1;
        if v_record_number = 0 then
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
          continue;
        end if;
        select b.k, b.d
          into v_previous_k, v_previous_d
          from (select *
                  from stock_transaction_data_all t
                 where t.code_ = v_code
                   and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc) b
         where rownum <= 1;
      
        -- 如果不是金叉，则从robot3_stock_filter表中删除这只股票
        if v_current_k >= v_current_d and v_previous_k <= v_previous_d then
          continue;
        else
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
        end if;
      
      end loop;
    end;
  end filter_by_kd_gold_cross;

  --------------------------------------------------------------------------------------------------

  -- 过滤条件：KD死叉。用于做空
  procedure filter_by_kd_dead_cross(p_date in varchar2) as
  begin
    declare
      -- 股票代码
      v_code varchar2(10);
      -- 当前k
      v_current_k number;
      -- 当前dea
      v_current_d number;
      -- 前一日k
      v_previous_k number;
      -- 前一日d
      v_previous_d number;
      -- 记录数量
      v_record_number number;
      -- 查询robot3_stock_filter表的全部记录
      cursor cur_robot_stock_filter is
        select * from robot3_stock_filter;
    begin
      for row_robot_stock_filter in cur_robot_stock_filter loop
        v_code := row_robot_stock_filter.stock_code;
      
        -- 当前k、d
        select count(*)
          into v_record_number
          from stock_transaction_data_all t
         where t.code_ = v_code
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
        if v_record_number = 0 then
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
          continue;
        end if;
        select t.k, t.d
          into v_current_k, v_current_d
          from stock_transaction_data_all t
         where t.code_ = v_code
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
      
        -- 前一日k、d
        select count(*)
          into v_record_number
          from (select *
                  from stock_transaction_data_all t
                 where t.code_ = v_code
                   and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc) b
         where rownum <= 1;
        if v_record_number = 0 then
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
          continue;
        end if;
        select b.k, b.d
          into v_previous_k, v_previous_d
          from (select *
                  from stock_transaction_data_all t
                 where t.code_ = v_code
                   and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc) b
         where rownum <= 1;
      
        -- 如果不是死叉，则从robot3_stock_filter表中删除这只股票
        if v_current_k <= v_current_d and v_previous_k >= v_previous_d then
          continue;
        else
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
        end if;
      
      end loop;
    end;
  end filter_by_kd_dead_cross;

  --------------------------------------------------------------------------------------------------

  -- 过滤条件：保留所有金叉。用于做多
  procedure filter_by_all_gold_cross(p_date in varchar2) as
  begin
    declare
      -- 股票代码
      v_code varchar2(10);
      -- 当前dif
      v_current_dif number;
      -- 当前dea
      v_current_dea number;
      -- 前一日dif
      v_previous_dif number;
      -- 前一日dea
      v_previous_dea number;
      -- 记录数量
      v_record_number number;
      -- 当前日期的收盘价
      v_current_close_price number;
      -- 当前日期的五日均线
      v_current_ma5 number;
      -- 前一日的收盘价
      v_previous_close_price number;
      -- 前一日的五日均线
      v_previous_ma5 number;
      -- 当前日期的ha开盘价
      v_current_ha_open_price number;
      -- 当前日期的ha收盘价
      v_current_ha_close_price number;
      -- 前一日的ha开盘价
      v_previous_ha_open_price number;
      -- 前一日的ha收盘价
      v_previous_ha_close_price number;
      -- 当前k
      v_current_k number;
      -- 当前dea
      v_current_d number;
      -- 前一日k
      v_previous_k number;
      -- 前一日d
      v_previous_d number;
      -- 查询robot3_stock_filter表的全部记录
      cursor cur_robot_stock_filter is
        select * from robot3_stock_filter;
    begin
      for row_robot_stock_filter in cur_robot_stock_filter loop
        v_code := row_robot_stock_filter.stock_code;
      
        -------------------------------------- 判断是否是MACD金叉 -----------------------------------
        -- 当前dif、dea
        select count(*)
          into v_record_number
          from stock_transaction_data_all t
         where t.code_ = v_code
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
        if v_record_number = 0 then
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
          continue;
        end if;
        select t.dif, t.dea
          into v_current_dif, v_current_dea
          from stock_transaction_data_all t
         where t.code_ = v_code
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
      
        -- 前一日dif、dea
        select count(*)
          into v_record_number
          from (select *
                  from stock_transaction_data_all t
                 where t.code_ = v_code
                   and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc) b
         where rownum <= 1;
        if v_record_number = 0 then
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
          continue;
        end if;
        select b.dif, b.dea
          into v_previous_dif, v_previous_dea
          from (select *
                  from stock_transaction_data_all t
                 where t.code_ = v_code
                   and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc) b
         where rownum <= 1;
      
        -- 如果是金叉则更新filter_type和direction字段
        if v_current_dif >= v_current_dea and
           v_previous_dif <= v_previous_dea then
          update robot3_stock_filter
             set filter_type = 1, direction = 1
           where stock_code = v_code;
          commit;
          continue;
        end if;
      
        -------------------------------------- 判断close_price否是金叉MA5 -----------------------------------
        -- 当日的收盘价和MA5
        begin
          select std.close_price, std.ma5
            into v_current_close_price, v_current_ma5
            from stock_transaction_data_all std
            join robot3_stock_filter rsf
              on rsf.stock_code = std.code_
             and std.code_ = row_robot_stock_filter.stock_code
             and std.date_ = to_date(p_date, 'yyyy-mm-dd');
        exception
          when no_data_found then
            delete from robot3_stock_filter t where t.stock_code = v_code;
            commit;
            continue;
        end;
      
        -- 前一日的收盘价和ma5
        begin
          select std.close_price, std.ma5
            into v_previous_close_price, v_previous_ma5
            from stock_transaction_data_all std
            join robot3_stock_filter rsf
              on rsf.stock_code = std.code_
             and std.code_ = row_robot_stock_filter.stock_code
             and std.date_ =
                 (select b.date_
                    from (select *
                            from stock_transaction_data_all std2
                           where std2.date_ < to_date(p_date, 'yyyy-mm-dd')
                           order by std2.date_ desc) b
                   where rownum <= 1);
        exception
          when no_data_found then
            delete from robot3_stock_filter t where t.stock_code = v_code;
            commit;
            continue;
        end;
      
        -- 判断收盘价金叉五日均线是否成立
        if v_current_close_price >= v_current_ma5 and
           v_previous_close_price <= v_previous_ma5 then
          update robot3_stock_filter
             set filter_type = 3, direction = 1
           where stock_code = v_code;
          commit;
          continue;
        end if;
      
        -------------------------------------- 判断hei_kin_ashi是否是上升趋势 -----------------------------------
        -- 当日的ha_open_price和ha_close_price
        begin
          select std.ha_open_price, std.ha_close_price
            into v_current_ha_open_price, v_current_ha_close_price
            from stock_transaction_data_all std
            join robot3_stock_filter rsf
              on rsf.stock_code = std.code_
             and std.code_ = row_robot_stock_filter.stock_code
             and std.date_ = to_date(p_date, 'yyyy-mm-dd');
        exception
          when no_data_found then
            delete from robot3_stock_filter t where t.stock_code = v_code;
            commit;
            continue;
        end;
      
        -- 前一日的ha_open_price和ha_close_price
        begin
          select std.ha_open_price, std.ha_close_price
            into v_previous_ha_open_price, v_previous_ha_close_price
            from stock_transaction_data_all std
            join robot3_stock_filter rsf
              on rsf.stock_code = std.code_
             and std.code_ = row_robot_stock_filter.stock_code
             and std.date_ =
                 (select b.date_
                    from (select *
                            from stock_transaction_data_all std2
                           where std2.date_ < to_date(p_date, 'yyyy-mm-dd')
                           order by std2.date_ desc) b
                   where rownum <= 1);
        exception
          when no_data_found then
            delete from robot3_stock_filter t where t.stock_code = v_code;
            commit;
            continue;
        end;
      
        -- 判断hei_kin_ashi上升趋势是否成立
        if v_current_ha_close_price >= v_current_ha_open_price and
           v_previous_ha_close_price <= v_previous_ha_open_price then
          update robot3_stock_filter
             set filter_type = 5, direction = 1
           where stock_code = v_code;
          commit;
          continue;
        end if;
      
        -------------------------------------- 判断KD是否是金叉 -----------------------------------
        -- 当前k、d
        select count(*)
          into v_record_number
          from stock_transaction_data_all t
         where t.code_ = v_code
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
        if v_record_number = 0 then
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
          continue;
        end if;
        select t.k, t.d
          into v_current_k, v_current_d
          from stock_transaction_data_all t
         where t.code_ = v_code
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
      
        -- 前一日k、d
        select count(*)
          into v_record_number
          from (select *
                  from stock_transaction_data_all t
                 where t.code_ = v_code
                   and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc) b
         where rownum <= 1;
        if v_record_number = 0 then
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
          continue;
        end if;
        select b.k, b.d
          into v_previous_k, v_previous_d
          from (select *
                  from stock_transaction_data_all t
                 where t.code_ = v_code
                   and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc) b
         where rownum <= 1;
      
        -- 如果不是金叉，则从robot3_stock_filter表中删除这只股票
        if v_current_k >= v_current_d and v_previous_k <= v_previous_d then
          update robot3_stock_filter
             set filter_type = 7, direction = 1
           where stock_code = v_code;
          commit;
          continue;
        end if;
      
        -- 如果不是任何一种金叉，则删除
        delete from robot3_stock_filter t where t.stock_code = v_code;
        commit;
      end loop;
    end;
  end filter_by_all_gold_cross;

  --------------------------------------------------------------------------------------------------

  -- 过滤条件：保留所有死叉。用于做空
  procedure filter_by_all_dead_cross(p_date in varchar2) as
  begin
    declare
      -- 股票代码
      v_code varchar2(10);
      -- 当前dif
      v_current_dif number;
      -- 当前dea
      v_current_dea number;
      -- 前一日dif
      v_previous_dif number;
      -- 前一日dea
      v_previous_dea number;
      -- 记录数量
      v_record_number number;
      -- 当前日期的收盘价
      v_current_close_price number;
      -- 当前日期的五日均线
      v_current_ma5 number;
      -- 前一日的收盘价
      v_previous_close_price number;
      -- 前一日的五日均线
      v_previous_ma5 number;
      -- 当前日期的ha开盘价
      v_current_ha_open_price number;
      -- 当前日期的ha收盘价
      v_current_ha_close_price number;
      -- 前一日的ha开盘价
      v_previous_ha_open_price number;
      -- 前一日的ha收盘价
      v_previous_ha_close_price number;
      -- 当前k
      v_current_k number;
      -- 当前dea
      v_current_d number;
      -- 前一日k
      v_previous_k number;
      -- 前一日d
      v_previous_d number;
      -- 查询robot3_stock_filter表的全部记录
      cursor cur_robot_stock_filter is
        select * from robot3_stock_filter t;
    begin
      for row_robot_stock_filter in cur_robot_stock_filter loop
        v_code := row_robot_stock_filter.stock_code;
      
        -------------------------------------- 判断是否是MACD死叉 -----------------------------------
        -- 当前dif、dea
        select count(*)
          into v_record_number
          from stock_transaction_data_all t
         where t.code_ = v_code
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
        if v_record_number = 0 then
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
          continue;
        end if;
        select t.dif, t.dea
          into v_current_dif, v_current_dea
          from stock_transaction_data_all t
         where t.code_ = v_code
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
      
        -- 前一日dif、dea
        select count(*)
          into v_record_number
          from (select *
                  from stock_transaction_data_all t
                 where t.code_ = v_code
                   and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc) b
         where rownum <= 1;
        if v_record_number = 0 then
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
          continue;
        end if;
        select b.dif, b.dea
          into v_previous_dif, v_previous_dea
          from (select *
                  from stock_transaction_data_all t
                 where t.code_ = v_code
                   and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc) b
         where rownum <= 1;
      
        -- 如果是金叉，则更新filter_type和direction字段
        if v_current_dif <= v_current_dea and
           v_previous_dif >= v_previous_dea then
          update robot3_stock_filter
             set filter_type = 2, direction = -1
           where stock_code = v_code;
          commit;
          continue;
        end if;
      
        -------------------------------------- 判断close_price否是死叉MA5 -----------------------------------
        -- 当日的收盘价和MA5
        begin
          select std.close_price, std.ma5
            into v_current_close_price, v_current_ma5
            from stock_transaction_data_all std
            join robot3_stock_filter rsf
              on rsf.stock_code = std.code_
             and std.code_ = row_robot_stock_filter.stock_code
             and std.date_ = to_date(p_date, 'yyyy-mm-dd');
        exception
          when no_data_found then
            delete from robot3_stock_filter t where t.stock_code = v_code;
            commit;
            continue;
        end;
      
        -- 前一日的收盘价和ma5
        begin
          select std.close_price, std.ma5
            into v_previous_close_price, v_previous_ma5
            from stock_transaction_data_all std
            join robot3_stock_filter rsf
              on rsf.stock_code = std.code_
             and std.code_ = row_robot_stock_filter.stock_code
             and std.date_ =
                 (select b.date_
                    from (select *
                            from stock_transaction_data_all std2
                           where std2.date_ < to_date(p_date, 'yyyy-mm-dd')
                           order by std2.date_ desc) b
                   where rownum <= 1);
        exception
          when no_data_found then
            delete from robot3_stock_filter t where t.stock_code = v_code;
            commit;
            continue;
        end;
      
        -- 判断收盘价死叉五日均线是否成立
        if v_current_close_price <= v_current_ma5 and
           v_previous_close_price >= v_previous_ma5 then
          update robot3_stock_filter
             set filter_type = 4, direction = -1
           where stock_code = v_code;
          commit;
          continue;
        end if;
      
        -------------------------------------- 判断hei_kin_ashi是否是下降趋势 -----------------------------------
        -- 当日的ha_open_price和ha_close_price
        begin
          select std.ha_open_price, std.ha_close_price
            into v_current_ha_open_price, v_current_ha_close_price
            from stock_transaction_data_all std
            join robot3_stock_filter rsf
              on rsf.stock_code = std.code_
             and std.code_ = row_robot_stock_filter.stock_code
             and std.date_ = to_date(p_date, 'yyyy-mm-dd');
        exception
          when no_data_found then
            delete from robot3_stock_filter t where t.stock_code = v_code;
            commit;
            continue;
        end;
      
        -- 前一日的ha_open_price和ha_close_price
        begin
          select std.ha_open_price, std.ha_close_price
            into v_previous_ha_open_price, v_previous_ha_close_price
            from stock_transaction_data_all std
            join robot3_stock_filter rsf
              on rsf.stock_code = std.code_
             and std.code_ = row_robot_stock_filter.stock_code
             and std.date_ =
                 (select b.date_
                    from (select *
                            from stock_transaction_data_all std2
                           where std2.date_ < to_date(p_date, 'yyyy-mm-dd')
                           order by std2.date_ desc) b
                   where rownum <= 1);
        exception
          when no_data_found then
            delete from robot3_stock_filter t where t.stock_code = v_code;
            commit;
            continue;
        end;
      
        -- 判断hei_kin_ashi下降趋势是否成立
        if v_current_ha_close_price <= v_current_ha_open_price and
           v_previous_ha_close_price >= v_previous_ha_open_price then
          update robot3_stock_filter
             set filter_type = 6, direction = -1
           where stock_code = v_code;
          commit;
          continue;
        end if;
      
        -------------------------------------- 判断KD是否是死叉 -----------------------------------
        -- 当前k、d
        select count(*)
          into v_record_number
          from stock_transaction_data_all t
         where t.code_ = v_code
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
        if v_record_number = 0 then
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
          continue;
        end if;
        select t.k, t.d
          into v_current_k, v_current_d
          from stock_transaction_data_all t
         where t.code_ = v_code
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
      
        -- 前一日k、d
        select count(*)
          into v_record_number
          from (select *
                  from stock_transaction_data_all t
                 where t.code_ = v_code
                   and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc) b
         where rownum <= 1;
        if v_record_number = 0 then
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
          continue;
        end if;
        select b.k, b.d
          into v_previous_k, v_previous_d
          from (select *
                  from stock_transaction_data_all t
                 where t.code_ = v_code
                   and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc) b
         where rownum <= 1;
      
        -- 如果不是死叉，则从robot3_stock_filter表中删除这只股票
        if v_current_k <= v_current_d and v_previous_k >= v_previous_d then
          update robot3_stock_filter
             set filter_type = 8, direction = -1
           where stock_code = v_code;
          commit;
          continue;
        end if;
      
        -- 如果没有任何一种死叉，则删除
        delete from robot3_stock_filter t where t.stock_code = v_code;
        commit;
      end loop;
    end;
  end filter_by_all_dead_cross;

  --------------------------------------------------------------------------------------------------

  -- 过滤条件：日线级别，某一段时间内，收盘价大于年线的记录数（数量更多）/收盘价小于年线的记录数（数量更少）<=p_rate
  procedure filter_by_c_p_gt_lt_ma250(p_begin_date in varchar2,
                                      p_end_date   in varchar2,
                                      p_rate       in number) as
  begin
    declare
      -- 收盘价大于ma250的记录数
      v_close_price_gt_ma250_count number;
      -- 收盘价小于ma250的记录数
      v_close_price_lt_ma250_count number;
      -- 股票代码
      row_code stock_transaction_data_all%rowtype;
      -- 所有股票代码
      cursor cur_stock_code is
        select distinct t.code_ from stock_transaction_data_all t;
    begin
      for row_code in cur_stock_code loop
      
        -- 收盘价大于年线的记录数
        select count(*)
          into v_close_price_gt_ma250_count
          from stock_transaction_data_all t
         where t.close_price > t.ma250
           and t.code_ = row_code.code_
           and t.date_ between to_date(p_begin_date, 'yyyy-mm-dd') and
               to_date(p_end_date, 'yyyy-mm-dd');
      
        -- 收盘价小于年线的记录数
        select count(*)
          into v_close_price_lt_ma250_count
          from stock_transaction_data_all t
         where t.close_price < t.ma250
           and t.code_ = row_code.code_
           and t.date_ between to_date(p_begin_date, 'yyyy-mm-dd') and
               to_date(p_end_date, 'yyyy-mm-dd');
      
        -- 如果比例大于v_rate，则删除这只股票
        if v_close_price_gt_ma250_count = 0 or
           v_close_price_lt_ma250_count = 0 then
          -- 删除这只股票
          delete from robot3_stock_filter t
           where t.stock_code = row_code.code_;
          commit;
        else
          if v_close_price_gt_ma250_count >= v_close_price_lt_ma250_count then
            if (v_close_price_gt_ma250_count / v_close_price_lt_ma250_count) >=
               p_rate then
              delete from robot3_stock_filter t
               where t.stock_code = row_code.code_;
              commit;
            end if;
          else
            if (v_close_price_lt_ma250_count / v_close_price_gt_ma250_count) >=
               p_rate then
              -- 删除这只股票
              delete from robot3_stock_filter t
               where t.stock_code = row_code.code_;
              commit;
            end if;
          end if;
        end if;
      end loop;
    end;
  end filter_by_c_p_gt_lt_ma250;

  --------------------------------------------------------------------------------------------------

  -- 过滤条件：如果当前交易日和前n个交易日的收盘价都大于/小于布林带上轨/下轨，并且当前交易日的收盘价小于/大于前一个交易日的收盘价，则保留
  procedure filter_by_up_down_boll(p_date                in varchar2,
                                   p_date_number         in number,
                                   p_operation_direction in number) as
  begin
    declare
      -- 股票代码
      v_code varchar2(50);
      -- 是否满足收盘价突破布林带上轨之后再出现调整走势的条件，1表示满足，0表示不满足
      v_top_boll_down number := 1;
      -- 是否满足收盘价跌破布林带下轨之后再出现反弹走势的条件，1表示满足，0表示不满足
      v_bottom_boll_up number := 1;
      -- 记录数量
      v_number number;
      -- 所有股票的当前交易日的code、收盘价、上轨、下轨，并且已经突破布林带上轨，或者已经跌破布林带下轨
      cursor cur_current_code_up_dn is
        select stda.code_, stda.close_price, stda.up, stda.dn_
          from stock_transaction_data_all stda
          join robot3_stock_filter rsf
            on stda.code_ = rsf.stock_code
           and stda.date_ = to_date(p_date, 'yyyy-mm-dd')
         where stda.close_price > stda.up
            or stda.close_price < stda.dn_;
      -- 某一只股票的上一个交易日的code、收盘价、上轨、下轨，并且已经突破布林带上轨，或者已经跌破布林带下轨
      cursor cur_last_code_up_dn is
        select code_, close_price, up, dn_
          from (select *
                  from (select *
                          from stock_transaction_data_all stda
                         where stda.date_ < to_date(p_date, 'yyyy-mm-dd')
                           and stda.code_ = v_code
                         order by stda.date_ desc)
                 where rownum <= p_date_number - 1) stda1
         where stda1.close_price > stda1.up
            or stda1.close_price < stda1.dn_;
    begin
      -- 当前交易日
      for row_current_code_up_dn in cur_current_code_up_dn loop
        v_code := row_current_code_up_dn.code_;
      
        -- 如果之前的p_date_number-1个交易日的收盘价并不是都在布林带上轨之上/下轨之下，则删除
        v_number := 0;
        for row_last_code_up_dn in cur_last_code_up_dn loop
          v_number := v_number + 1;
        end loop;
        if v_number != (p_date_number - 1) then
          delete from robot3_stock_filter t where t.stock_code = v_code;
          commit;
        end if;
      
        -- 当前交易日和前一个交易日的收盘价都在布林带上轨之上，并且当前交易日的收盘价小于前一个交易日的收盘价
        if p_operation_direction = 0 or p_operation_direction = 2 then
          v_top_boll_down := 1;
          for row_last_code_up_dn in cur_last_code_up_dn loop
          
            if row_current_code_up_dn.close_price >
               row_current_code_up_dn.up and
               row_current_code_up_dn.close_price <
               row_last_code_up_dn.close_price and
               row_last_code_up_dn.close_price > row_last_code_up_dn.up then
              null;
            else
              v_top_boll_down := 0;
            end if;
            exit;
          end loop;
        
          -- 标记为做空
          if v_top_boll_down = 1 then
            update robot3_stock_filter t
               set t.direction = -1, t.filter_type = 10
             where t.stock_code = v_code;
            commit;
          end if;
        end if;
      
        -- 当前交易日和前一个交易日的收盘价都在布林带上轨之下，并且当前交易日的收盘价大于前一个交易日的收盘价
        if p_operation_direction = 0 or p_operation_direction = 1 then
          v_bottom_boll_up := 1;
          for row_last_code_up_dn in cur_last_code_up_dn loop
            if row_current_code_up_dn.close_price <
               row_current_code_up_dn.dn_ and
               row_current_code_up_dn.close_price >
               row_last_code_up_dn.close_price and
               row_last_code_up_dn.close_price < row_last_code_up_dn.dn_ then
              null;
            else
              v_bottom_boll_up := 0;
            end if;
            exit;
          end loop;
        
          if v_bottom_boll_up = 1 then
            -- 标记为做多
            update robot3_stock_filter t
               set t.direction = 1, t.filter_type = 9
             where t.stock_code = v_code;
            commit;
          end if;
        end if;
      end loop;
    
      -- 删除不满足条件的股票
      delete from robot3_stock_filter t
       where t.direction is null
         and t.filter_type is null;
      commit;
    end;
  end filter_by_up_down_boll;

  --------------------------------------------------------------------------------------------------

  -- 买股票/卖股票，只向robot_stock_transaction_record表中插入记录，只更新robot_account表的hold_stock_number字段
  procedure buy_or_sell(p_buy_date                   in varchar2,
                        p_backward_month             in number,
                        p_average_date_number        in number,
                        p_success_rate_type          in number,
                        p_success_rate_or_percentage in number,
                        p_direction                  in number,
                        p_shipment_space_control     in number,
                        p_percentage_top_threshold   in number,
                        p_shipping_space             in number,
                        p_hold_stock_number          in number,
                        p_operation_direction        in number) as
  begin
    declare
      -- robot_stock_filter表类型的变量
      row_robot_stock_filter robot3_stock_filter%rowtype;
      -- 收盘价
      v_close_price number;
      -- 买入/卖出多少股
      v_buy_or_sell_amount number;
      -- 当前的资金资产
      v_capital_assets number;
      -- 当前的总资产
      v_total_assets number;
      -- 当前持有股票的数量
      v_hold_stock_number number;
      -- 调用存储过程的返回值
      v_macd_success_rate_array T_MACD_SUCCESS_RATE_ARRAY;
      -- 调用存储过程的返回值
      v_c_p_ma5_success_rate_array T_C_P_MA5_SUCCESS_RATE_ARRAY;
      -- 调用存储过程的返回值
      v_h_k_a_success_rate_arr T_H_K_A_SUCCESS_RATE_ARRAY;
      -- 当前的成功率（有延迟）
      -- v_current_success_rate number;
      -- 最大成功率
      v_max_success_rate number;
      -- 最小成功率
      v_min_success_rate number;
      -- 可以有的最大仓位
      v_max_shipment_space number;
      -- 返回机器人账户
      cursor cur_robot_account is
        select * from robot3_account;
    
    begin
      -- 如果p_shipment_space_control为0，则不控制仓位；如果p_shipment_space_control为1，控制仓位（成功率）；
      -- 如果p_shipment_space_control为2，控制仓位（百分比），并且p_shipping_space就是仓位
      if p_shipment_space_control = 0 then
        v_max_shipment_space := p_hold_stock_number;
      end if;
    
      if p_shipment_space_control = 1 then
        v_max_shipment_space := ceil(p_success_rate_or_percentage /
                                     p_hold_stock_number);
        if v_max_shipment_space = 0 then
          v_max_shipment_space := 1;
        end if;
      end if;
    
      if p_shipment_space_control = 2 then
        v_max_shipment_space := p_shipping_space;
      end if;
      SCOTT.pkg_tool.DEBUG('仓位是：' || v_max_shipment_space);
    
      for row_robot_account in cur_robot_account loop
        -- 如果当前账户的持股数量已经大于等于10，或者最大仓位小于等于持股数量，则查找下一个机器人
        if row_robot_account.HOLD_STOCK_NUMBER >= p_hold_stock_number or
           v_max_shipment_space <= row_robot_account.HOLD_STOCK_NUMBER then
          continue;
        end if;
      
        -- 当前账户的资金资产、总资产和持股数量
        select t.capital_assets, t.total_assets, t.hold_stock_number
          into v_capital_assets, v_total_assets, v_hold_stock_number
          from robot3_account t
         where t.robot_name = row_robot_account.robot_name;
      
        while true loop
          -- 随机取一条记录，robot_stock_filter表中已经没有数据了，则查找下一个机器人
          begin
            select *
              into row_robot_stock_filter
              from (select *
                      from robot3_stock_filter
                     order by dbms_random.random)
             where rownum = 1;
          exception
            when no_data_found then
              DBMS_OUTPUT.put_line('robot_stock_filter表中已经没有数据了！');
              exit;
          end;
        
          -- 查找这只股票的收盘价
          select t.close_price
            into v_close_price
            from stock_transaction_data_all t
           where t.code_ = row_robot_stock_filter.stock_code
             and t.date_ = to_date(p_buy_date, 'yyyy-mm-dd');
        
          -- 如果股票的收盘价过高，或者资金资产太少，连一手也买不了，则直接查找下一个机器人
          exit when v_close_price * 100 > v_capital_assets;
        
          -- 如果当前账户的持股数量大于等于10，或者最大仓位小于小于等于持股数量，则直接查找下一个机器人
          exit when v_hold_stock_number >= p_hold_stock_number or v_max_shipment_space <= v_hold_stock_number;
        
          -- 如果可以买，则向robot_stock_transaction_record表中插入数据，从robot_stock_filter表中删除这条记录。
          -- 计算买多少股
          v_buy_or_sell_amount := 100;
          while v_capital_assets >= (v_buy_or_sell_amount * v_close_price) loop
            exit when(v_buy_or_sell_amount * v_close_price) >=(v_total_assets /
                                                               p_hold_stock_number);
            v_buy_or_sell_amount := v_buy_or_sell_amount + 100;
          end loop;
          if v_capital_assets < (v_buy_or_sell_amount * v_close_price) then
            v_buy_or_sell_amount := v_buy_or_sell_amount - 100;
          end if;
        
          -- 向robot_stock3_transact_record表中插入数据
          -- 做多
          if p_operation_direction = 0 or p_operation_direction = 1 then
            if row_robot_stock_filter.direction = 1 then
              insert into robot3_stock_transact_record
                (robot_name,
                 stock_code,
                 buy_date,
                 buy_price,
                 buy_amount,
                 filter_type,
                 direction)
              values
                (row_robot_account.robot_name,
                 row_robot_stock_filter.stock_code,
                 to_date(p_buy_date, 'yyyy-mm-dd'),
                 v_close_price,
                 v_buy_or_sell_amount,
                 row_robot_stock_filter.filter_type,
                 row_robot_stock_filter.direction);
            end if;
          end if;
          -- 做空
          if p_operation_direction = 0 or p_operation_direction = 2 then
            if row_robot_stock_filter.direction = -1 then
              insert into robot3_stock_transact_record
                (robot_name,
                 stock_code,
                 sell_date,
                 sell_price,
                 sell_amount,
                 filter_type,
                 direction)
              values
                (row_robot_account.robot_name,
                 row_robot_stock_filter.stock_code,
                 to_date(p_buy_date, 'yyyy-mm-dd'),
                 v_close_price,
                 v_buy_or_sell_amount,
                 row_robot_stock_filter.filter_type,
                 row_robot_stock_filter.direction);
            end if;
          end if;
          commit;
        
          -- 计算这支股票的持股数量、资金资产
          v_hold_stock_number := v_hold_stock_number + 1;
          v_capital_assets    := v_capital_assets -
                                 (v_close_price * v_buy_or_sell_amount);
        
          -- 从robot3_stock_filter表中删除这条记录
          delete from robot3_stock_filter t
           where t.stock_code = row_robot_stock_filter.stock_code;
          commit;
        end loop;
      end loop;
    end;
  end buy_or_sell;

  --------------------------------------------------------------------------------------------------

  -- 根据当日买入股票/卖出股票的收盘价，在买股票/卖股票之后，更新robot_account表
  -- 存储过程名称：update_robot_account_after_buy_or_sell
  procedure update_robot_account_after_b_s(p_date in varchar2) as
  begin
    declare
      -- 机器人账号
      v_robot_name varchar2(20);
      -- 股票资产
      v_stock_assets number;
      -- 卖出/买入股票的数量
      v_sell_or_buy_stock_number number;
      -- 股票的收盘价
      v_current_close_price number;
      -- 表robot3_stock_transact_record类型的记录
      row_robot_stock_tranc_record robot3_stock_transact_record%rowtype;
      -- 返回机器人账户
      cursor cur_robot_account is
        select * from robot3_account;
      -- 计算每一个机器人账户，在某一天卖完股票后的收益
      cursor cur_bull_stock is
        select *
          from robot3_stock_transact_record rstr
         where rstr.robot_name = v_robot_name
           and rstr.direction = 1
           and rstr.sell_date is null
           and rstr.sell_price is null
           and rstr.sell_amount is null;
      -- 计算每一个机器人账户，在某一天买完股票后的收益
      cursor cur_short_stock is
        select *
          from robot3_stock_transact_record rstr
         where rstr.robot_name = v_robot_name
           and rstr.direction = -1
           and rstr.buy_date is null
           and rstr.buy_price is null
           and rstr.buy_amount is null;
    begin
      ----------------------------------- 做多和做空 ---------------------------------
      for row_robot_account in cur_robot_account loop
        -- 机器人账户名称
        v_robot_name               := row_robot_account.robot_name;
        v_stock_assets             := 0;
        v_sell_or_buy_stock_number := 0;
        v_current_close_price      := null;
      
        -- 做多
        for row_robot_stock_tranc_record in cur_bull_stock loop
        
          -- 查找某只股票在某一天的收盘价
          begin
            select t.close_price
              into v_current_close_price
              from stock_transaction_data_all t
             where t.code_ = row_robot_stock_tranc_record.stock_code
               and t.date_ = to_date(p_date, 'yyyy-mm-dd');
          exception
            when no_data_found then
              v_current_close_price := null;
              -- 说明股票在这一天没有交易记录
              DBMS_OUTPUT.put_line('股票【' ||
                                   row_robot_stock_tranc_record.stock_code ||
                                   '】在日期【' || p_date || '】没有交易记录');
          end;
          -- 如果在某一天没有收盘价，比如停牌，则查找最近一个交易日的收盘价
          if v_current_close_price is null then
            select std.close_price
              into v_current_close_price
              from (select *
                      from stock_transaction_data_all t
                     where t.code_ = row_robot_stock_tranc_record.stock_code
                       and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                     order by t.date_ desc) std
             where rownum <= 1;
          end if;
        
          -- 卖完股票后的收益
          v_stock_assets             := v_stock_assets +
                                        v_current_close_price *
                                        row_robot_stock_tranc_record.buy_amount;
          v_sell_or_buy_stock_number := v_sell_or_buy_stock_number + 1;
        end loop;
      
        -- 做空
        for row_robot_stock_tranc_record in cur_short_stock loop
        
          -- 查找某只股票在某一天的收盘价
          begin
            select t.close_price
              into v_current_close_price
              from stock_transaction_data_all t
             where t.code_ = row_robot_stock_tranc_record.stock_code
               and t.date_ = to_date(p_date, 'yyyy-mm-dd');
          exception
            when no_data_found then
              v_current_close_price := null;
              -- 说明股票在这一天没有交易记录
              DBMS_OUTPUT.put_line('股票【' ||
                                   row_robot_stock_tranc_record.stock_code ||
                                   '】在日期【' || p_date || '】没有交易记录');
          end;
          -- 如果在某一天没有收盘价，比如停牌，则查找最近一个交易日的收盘价
          if v_current_close_price is null then
            select std.close_price
              into v_current_close_price
              from (select *
                      from stock_transaction_data_all t
                     where t.code_ = row_robot_stock_tranc_record.stock_code
                       and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                     order by t.date_ desc) std
             where rownum <= 1;
          end if;
        
          -- 买完股票后的收益
          v_stock_assets             := v_stock_assets +
                                        (row_robot_stock_tranc_record.sell_price -
                                        v_current_close_price) *
                                        row_robot_stock_tranc_record.sell_amount +
                                        row_robot_stock_tranc_record.sell_price *
                                        row_robot_stock_tranc_record.sell_amount;
          v_sell_or_buy_stock_number := v_sell_or_buy_stock_number + 1;
        end loop;
      
        -- 更新robot3_account表
        update robot3_account t
           set t.hold_stock_number = v_sell_or_buy_stock_number,
               t.stock_assets      = v_stock_assets,
               t.capital_assets    = t.total_assets - v_stock_assets
         where t.robot_name = v_robot_name;
        commit;
      
        -- 向表robot3_account_log中插入数据
        select *
          into row_robot_account
          from robot3_account t
         where t.robot_name = row_robot_account.robot_name;
        insert into robot3_account_log
          (DATE_,
           ROBOT_NAME,
           HOLD_STOCK_NUMBER,
           STOCK_ASSETS,
           CAPITAL_ASSETS,
           TOTAL_ASSETS)
        values
          (to_date(p_date, 'yyyy-mm-dd'),
           row_robot_account.robot_name,
           row_robot_account.hold_stock_number,
           row_robot_account.stock_assets,
           row_robot_account.capital_assets,
           row_robot_account.total_assets);
        commit;
      end loop;
    end;
    /*begin
    declare
      -- 机器人名称
      v_robot_name varchar2(20);
      -- 某个机器人账号，在某一天买入股票/卖出股票的数量
      v_buy_or_sell_stock_number number := 0;
      -- 买股票/卖股票花的钱数
      v_b_s_stock_capital_assets number := 0;
      -- 返回机器人账户
      cursor cur_robot_account is
        select * from robot_account;
    begin
      for row_robot_account in cur_robot_account loop
        v_robot_name := row_robot_account.robot_name;
      
        -- 重置为0
        v_b_s_stock_capital_assets := 0;
      
        ----------------------------------- 做多和做空 ---------------------------------
        -- 查找日期p_date时，持股数量、买股票和卖股票花的钱数
        begin
          select count(*),
                 nvl(sum(rstr.buy_price * rstr.buy_amount), 0) +
                 nvl(sum(rstr.sell_price * rstr.sell_amount), 0)
            into v_buy_or_sell_stock_number, v_b_s_stock_capital_assets
            from robot_stock_transaction_record rstr
           where rstr.robot_name = v_robot_name
                -- and rstr.buy_date = to_date(p_date, 'yyyy-mm-dd')
             and ((rstr.sell_date is null and rstr.sell_price is null and
                 rstr.sell_amount is null and rstr.direction = 1) or
                 (rstr.buy_date is null and rstr.buy_price is null and
                 rstr.buy_amount is null and rstr.direction = -1));
        exception
          when no_data_found then
            DBMS_OUTPUT.put_line('机器人账户【' || v_robot_name || '】没有任何股票');
            exit;
        end;
      
        -- 更新持股数量、股票资产、资金资产
        if v_buy_or_sell_stock_number is not null and
           v_b_s_stock_capital_assets is not null then
          update robot_account t
             set t.hold_stock_number = v_buy_or_sell_stock_number,
                 t.stock_assets      = v_b_s_stock_capital_assets,
                 t.capital_assets    = t.total_assets -
                                       v_b_s_stock_capital_assets
           where t.robot_name = row_robot_account.robot_name;
          commit;
        end if;*/
  
    /*----------------------------------- 做空 -----------------------------------
    -- 查找日期p_date时，持股数量、买股票/卖股票花的钱数
    begin
      select count(*), sum(rstr.sell_price * rstr.sell_amount)
        into v_buy_or_sell_stock_number, v_b_s_stock_capital_assets
        from robot_stock_transaction_record rstr
       where rstr.robot_name = v_robot_name
         and rstr.sell_date = to_date(p_date, 'yyyy-mm-dd')
         and rstr.buy_date is null
         and rstr.buy_price is null
         and rstr.buy_amount is null
         and rstr.direction = -1;
    exception
      when no_data_found then
        DBMS_OUTPUT.put_line('机器人账户【' || v_robot_name || '】没有任何股票');
        exit;
    end;
    
    -- 如果没有买股票，则直接返回下一条记录
    if v_buy_or_sell_stock_number is null or
       v_b_s_stock_capital_assets is null then
      continue;
    end if;
    
    -- 更新持股数量、股票资产、资金资产
    update robot_account t
       set t.hold_stock_number = t.hold_stock_number +
                                 v_buy_or_sell_stock_number,
           t.stock_assets      = t.stock_assets +
                                 v_b_s_stock_capital_assets,
           t.capital_assets    = t.capital_assets -
                                 v_b_s_stock_capital_assets
     where t.robot_name = row_robot_account.robot_name;
    commit;*/
  
    /*end loop;
    end;*/
  end update_robot_account_after_b_s;

end PKG_ROBOT3;